import { QueryTypes } from "sequelize";
import { sequelize } from "../lib/database.js";
import logger from "../lib/log4js.js";
import { RuFormMapper } from "./ru-form.ts";
import { USER_TYPES } from "../entity/common-defines.ts";
import { WeightValueChecker } from './weight-value-checker.ts'
import { EvaProjectMapper } from "./eva-project.ts";
import { A0_TOP } from "../entity/common-defines.ts";
import { XlsxData } from "./xlsx-data.ts";
import { roundNumber } from "../lib/util.js";

class ResultLeadersMapper extends XlsxData {

	private formMapper: RuFormMapper = new RuFormMapper;
	private checker: WeightValueChecker = new WeightValueChecker;
	private projectMapper: EvaProjectMapper = new EvaProjectMapper;
	public async getResultData(project_id: string, test: boolean): Promise<SheetData> {

		const form: any = await this.formMapper.findOneByQuery({ where: { user_type: USER_TYPES.LEADERS } })
		const formId: string = form?.id;
		if (!formId) throw new Error("对应测评表不存在")
		const added_value = await this.checker.findAddedScoreValue(project_id, formId);
		const sql = `             
select
	user_name,
	amount,
	round(sum(case when rule_name = '政治能力' then rule_vote_value end),2) as 政治能力,
	round(sum(case when rule_name = '政治表现' then rule_vote_value end),2) as 政治表现,
	round(sum(case when rule_name = '创新精神' then rule_vote_value end),2) as 创新精神,
	round(sum(case when rule_name = '创新成果' then rule_vote_value end),2) as 创新成果,
	round(sum(case when rule_name = '专业素养' then rule_vote_value end),2) as 专业素养,
	round(sum(case when rule_name = '领导能力' then rule_vote_value end),2) as 领导能力,
	round(sum(case when rule_name = '担当作为' then rule_vote_value end),2) as 担当作为,
	round(sum(case when rule_name = '履职绩效' then rule_vote_value end),2) as 履职绩效,
	round(sum(case when rule_name = '一岗双责' then rule_vote_value end),2) as 一岗双责,
	round(sum(case when rule_name = '廉洁从业' then rule_vote_value end),2) as 廉洁从业
from
	(
	-- 第二层:获取某个指标所有主体和
	select
		form_id,
		type_id,
		rule_id,
		user_name,
		rule_name,
		sum(vote_amount) amount,
		sum(type_vote_value) rule_vote_value
	from
		(
		--  第一层:获取每个指标平均值加权和
		select
			rfr.form_id,
			on2.type_id,
			on2.type_name,
			ea.rule_id,
			ea.rule_name,
			ea.user_name,
			sum(1) vote_amount,			
			(sum(convert(json_value(ifnull(ea.rule_value, '[0]'), '$[0]'), int)) /sum(1)) avg_value,
			(sum(convert(json_value(ifnull(ea.rule_value, '[0]'), '$[0]'), int)) /sum(1)) * if(rfr.weight_value is null or rfr.weight_value =0,1,rfr.weight_value ) /100 type_vote_value
		from
			eva_answer ea
		inner join eva_project_owner epo on
			epo.project_id = ea.project_id
			and epo.id = ea.project_owner_id
		inner join owner_name on2 on
			on2.id = epo.owner_name_id
		inner join eva_project ep on ep.id=?
		inner join ru_form_owner_type rfr on
			rfr.form_id = ea.rule_form_id
			and rfr.type_id = on2.type_id
			and rfr.group_type=ep.group_type
		where
		    ea.is_test is ?
			and ea.project_id = ?
			and ea.user_type = ?
			and ea.rule_form_id = ?
		group by
			ea.user_id,
			rule_id,
			type_id
  ) twn
	group by
	    twn.user_name,
		twn.rule_id desc 
) ea_leader
group by
	user_name
        
        `
		try {
			const result: Array<LeaderResult> = await sequelize.query(sql, {
				replacements: [test, project_id, project_id, USER_TYPES.LEADERS, formId],
				type: QueryTypes.SELECT
			})
			if (result && result.length > 0) {
				const data = result.map(({ user_name, amount, 政治能力, 政治表现, 创新精神, 创新成果, 专业素养, 领导能力, 担当作为, 履职绩效, 一岗双责, 廉洁从业 }) => (
					{
						user_name,
						amount,
						政治能力: 政治能力 + added_value,
						政治表现: 政治表现 + added_value,
						创新精神: 创新精神 + added_value,
						创新成果: 创新成果 + added_value,
						专业素养: 专业素养 + added_value,
						领导能力: 领导能力 + added_value,
						担当作为: 担当作为 + added_value,
						履职绩效: 履职绩效 + added_value,
						一岗双责: 一岗双责 + added_value,
						廉洁从业: 廉洁从业 + added_value
					}
				));
				const projectInfo: any = await this.projectMapper.findOne({ pk: project_id });
				return { title: `${projectInfo.object_group_name}-${projectInfo.ef_year}年${form.name}`, name: form.name, data };
			}

		} catch (error) {
			logger.error(error);
		}
		return { data: [] };
	}


	async exportXlsxSheet(test: boolean, project_id: string, rowStart: number, index: number): Promise<SheetMeta> {

		const { title, name, data } = await this.getResultData(project_id, test);
		if (data.length === 0) {
			return { merges: [], data: [], name };
		}
		const result_data = [];
		const title_names = [`${index + 1}.${title}`, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""];
		const top_names = ["姓名", "有效票", "排名", "加权汇总"];
		for (const [key, value] of Object.entries(A0_TOP)) {
			top_names.push(key);
			for (const vk of value.items) {
				top_names.push(" ");
			}
		}
		result_data.push(title_names);
		result_data.push(top_names);
		const child_names: String[] = ['', '', '', ''];
		for (const key in A0_TOP) {
			const item_value = A0_TOP[key];
			child_names.push("小计");
			for (const lkey of item_value.items) {
				child_names.push(lkey);
			}
		}
		result_data.push(child_names);
		const rows = [];
		for (const lv_data of data) {
			const child_data = [];
			child_data[0] = lv_data.user_name;
			child_data[1] = lv_data.amount,
				child_data[2] = 0;
			child_data[3] = 0;
			const whole_value = [];
			for (const key in A0_TOP) {
				const item_value = A0_TOP[key];
				const keys = item_value.items;
				const nVal = keys.map((n: any, i: number) => lv_data[n] * item_value.child_rate[i] / 100).reduce((a, b) => a + b)
				whole_value.push(roundNumber(nVal * item_value.rate / 100));
				child_data.push(roundNumber(nVal));
				for (const nkey of keys) {
					child_data.push(lv_data[nkey]);
				}
			}
			const wv = whole_value.reduce((a, b) => a + b);
			child_data[3] = wv;
			rows.push(child_data);
		}

		rows.sort((a, b) => {
			if (isNaN(a[3]) || isNaN(b[3])) {
				return 100;
			} else {
				return parseFloat(b[3]) - parseFloat(a[3]);
			}
		})
		for (let r = 0; r < rows.length; r++) {
			const row = rows[r];
			row[2] = r + 1;
			result_data.push(row);
		}
		let mc = 4;
		const merges = [

			{ s: { c: 0, r: rowStart }, e: { c: 18, r: rowStart } },

			{ s: { c: 0, r: rowStart + 1 }, e: { c: 0, r: rowStart + 2 } },
			{ s: { c: 1, r: rowStart + 1 }, e: { c: 1, r: rowStart + 2 } },
			{ s: { c: 2, r: rowStart + 1 }, e: { c: 2, r: rowStart + 2 } },
			{ s: { c: 3, r: rowStart + 1 }, e: { c: 3, r: rowStart + 2 } }

		];
		for (const cary of Object.values(A0_TOP)) {
			merges.push({ s: { c: mc, r: rowStart + 1 }, e: { c: mc + cary.items.length, r: rowStart + 1 } });
			mc = mc + cary.items.length + 1;
		}
		return { merges, data: result_data, name }
	}
}

export { ResultLeadersMapper }